from typing import Optional, List
from datetime import datetime
from api import sqldf

def get_storage_records(
    search: Optional[str] = None,
    storage_type: Optional[str] = None,
    start_date: Optional[str] = None,
    end_date: Optional[str] = None,
    page: int = 1,
    page_size: int = 10
) -> dict:
    """
    获取存储记录列表，支持多字段搜索
    """
    try:
        # 构建基础查询
        base_query = """
            SELECT s.StorageNumber as storageNumber,
                   s.MaterialCode as code,
                   s.Quantity as quantity,
                   s.StorageType as storageType,
                   s.Remark as remark,
                   s.CreateBy as operator,
                   s.CreateTime as createTime,
                   s.Accumulation as accumulation,
                   p.Type as type,
                   p.Description as description,
                   p.Unit as unit
            FROM StorageRecord s
            LEFT JOIN PackageMaterial p ON s.MaterialCode = p.Code
            WHERE 1=1
        """

        conditions = []
        # 添加搜索条件
        if search:
            conditions.append(f"""(
                s.StorageNumber LIKE '%{search}%'
                OR s.MaterialCode LIKE '%{search}%'
                OR p.Type LIKE '%{search}%'
                OR p.Description LIKE '%{search}%'
            )""")

        # 添加存储类型条件
        if storage_type:
            conditions.append(f"s.StorageType = '{storage_type}'")

        # 添加日期范围条件
        if start_date:
            conditions.append(f"CONVERT(date, s.CreateTime) >= '{start_date}'")
        if end_date:
            conditions.append(f"CONVERT(date, s.CreateTime) <= '{end_date}'")

        # 组合所有条件
        if conditions:
            base_query += " AND " + " AND ".join(conditions)

        # 获取总记录数
        count_query = f"SELECT COUNT(*) as total FROM ({base_query}) t"
        total_result = sqldf.Query(count_query)
        total = int(total_result.iloc[0]['total']) if not total_result.empty else 0

        # 添加排序和分页
        query = base_query + f" ORDER BY s.CreateTime DESC OFFSET {(page - 1) * page_size} ROWS FETCH NEXT {page_size} ROWS ONLY"
        
        # 执行查询
        result = sqldf.Query(query)
        
        if result is None or result.empty:
            return {
                "records": [],
                "total": 0
            }

        # 转换结果为字典列表
        records = result.to_dict('records')
        
        # 格式化日期时间
        for record in records:
            if 'createTime' in record and record['createTime']:
                record['createTime'] = record['createTime'].strftime("%Y-%m-%d %H:%M:%S")
            if 'quantity' in record:
                record['quantity'] = float(record['quantity'])

        return {
            "records": records,
            "total": total
        }

    except Exception as e:
        print(f"Error in get_storage_records: {str(e)}")
        raise e 